package util.io;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;

/**
 * 功能:
 * 作者: 洪世成
 * 日期: 2017/10/17
 * 版权所有: admin
 */
public class PoiExcelCreateUtil {
    private static final Logger log = LoggerFactory.getLogger(PoiExcelCreateUtil.class);

    public static final String FONT_NAME_SONG = "宋体";
    public static final short FONT_HEIGHT_DEFAULT = 300;
    public static final short FONT_WEIGHT_BOLE = Font.BOLDWEIGHT_BOLD;
    public static final short FONT_WEIGHT_NORMAL = Font.BOLDWEIGHT_NORMAL;
    public static final String FORMAT_DATE_DEFAULT = "yyyy-MM-dd";
    public static final String FORMAT_DATE_TIME_DEFAULT = "yyyy-MM-dd hh:mm:ss";
    public static final String FORMAT_MONEY_DEFAULT = "¥#,##0.00";
    public static final String FORMAT_TEXT_DEFAULT = "@";//文本类型
    public static final int COLUMN_WIDTH_DEFAULT = 4000;
    public static final short ROW_HEIGHT_DEFAULT = 400;


    private HSSFWorkbook hssfWorkbook;
    private HSSFSheet hssfSheet;
    private String title;
    private String[] headName;
    private String[] fieldName;
    private int[] columnWidth;
    private CellStyle titleStyle;
    private CellStyle headStyle;
    private CellStyle[] columnStyle;
    private List dataList;
    private int columnSize;


    public PoiExcelCreateUtil(int columnSize) {
        this.columnSize = columnSize;
        this.hssfWorkbook = new HSSFWorkbook();
    }

    public int getColumnSize() {
        return columnSize;
    }

    public void setColumnSize(int columnSize) {
        this.columnSize = columnSize;
    }

    /**
     * 创建sheet
     *
     * @param sheetName
     * @param title
     * @param headName
     * @param fieldName
     * @param columnWidth
     * @param titleStyle
     * @param headStyle
     * @param columnStyle
     * @param dataList
     */
    public void createSheet(String sheetName, String title, String[] headName, String[] fieldName, int[] columnWidth, CellStyle titleStyle, CellStyle headStyle, CellStyle[] columnStyle, List dataList) {
        if (headName.length < columnSize || fieldName.length < columnSize || columnStyle.length < columnSize || columnWidth.length < columnSize) {
            throw new RuntimeException("headName,fieldName,columnWidth,columnStyle 存在长度小于columnSize");
        }
        if (org.apache.commons.lang.StringUtils.isEmpty(sheetName)) {
            this.hssfSheet = this.hssfWorkbook.createSheet();
        } else {
            this.hssfSheet = this.hssfWorkbook.createSheet(sheetName);
        }
        this.headName = headName;
        this.title = title;
        this.fieldName = fieldName;
        this.columnWidth = columnWidth;
        this.columnStyle = columnStyle;
        this.titleStyle = titleStyle;
        this.headStyle = headStyle;
        this.dataList = dataList;

        int startIndex = 0;
        if (title != null) {
            writeTitle(startIndex++); // 写入表头 head
        }
        writeHead(startIndex++); // 写入 head
        writeBody(startIndex++); // 写入 body
    }


    /**
     * 获取单元格样式
     *
     * @param fontHeight
     * @param fontWeight
     * @param fontName
     * @return
     */
    private HSSFCellStyle createCellStyle(short fontHeight, short fontWeight, String fontName) {
        // 单元格的样式
        HSSFCellStyle cellStyle = this.hssfWorkbook.createCellStyle();
        HSSFFont font = this.hssfWorkbook.createFont();
        font.setFontHeight(fontHeight);
        font.setBoldweight(fontWeight);
        font.setFontName(fontName);
        cellStyle.setFont(font);
        cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 单元格垂直居中
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 单元格水平居中
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        return cellStyle;
    }

    /**
     * 获取单元格样式
     *
     * @param fontHeight
     * @param fontWeight
     * @param fontName
     * @param format
     * @return
     */
    public HSSFCellStyle createCellStyle(short fontHeight, short fontWeight, String fontName, String format) {
        if (StringUtils.isBlank(format)){
            format=FORMAT_TEXT_DEFAULT;
        }
        HSSFCellStyle hssfCellStyle = createCellStyle(fontHeight, fontWeight, fontName);
        HSSFDataFormat hssfDataFormat = this.hssfWorkbook.createDataFormat();
        hssfCellStyle.setDataFormat(hssfDataFormat.getFormat(format));
        return hssfCellStyle;
    }


    /**
     * 创建单元格
     *
     * @param hssfRow
     * @param columnIndex
     * @param value
     * @param flag
     * @return
     */
    private HSSFCell createHSSFCell(HSSFRow hssfRow, int columnIndex, Object value, boolean isBody) {
        HSSFCell cell = hssfRow.createCell(columnIndex);
        setCellValueHandler(cell, value);
        if (isBody) {
            cell.setCellStyle(this.columnStyle[columnIndex]);
        } else {
            cell.setCellStyle(this.headStyle);
        }
        this.hssfSheet.setColumnWidth(columnIndex, this.columnWidth[columnIndex]);//列宽度
        return cell;
    }


    /**
     * 写标题
     *
     * @param startIndex
     */
    private void writeTitle(int startIndex) {
        log.info("【写入标题】");
        // 处理单元格合并，四个参数分别是：起始行，终止行，起始列，终止列
        this.hssfSheet.addMergedRegion(new CellRangeAddress(startIndex, startIndex, (short) 0, this.columnSize - 1));
        HSSFRow titleRow = this.hssfSheet.createRow(startIndex);
        titleRow.setHeight((short) 1000);
        HSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(this.title);
        titleCell.setCellStyle(this.titleStyle);

    }

    /**
     * 写表头
     *
     * @param startIndex
     */
    private void writeHead(int startIndex) {
        log.info("【写入表头】");
        HSSFRow hssfRow = this.hssfSheet.createRow(startIndex);
        hssfRow.setHeight((short) 1000);
        for (int i = 0; i < columnSize; i++) {
            createHSSFCell(hssfRow, i, headName[i], false);
        }
    }

    /**
     * 写内容
     *
     * @param startIndex
     */
    private void writeBody(int startIndex) {
        log.info("【写入内容】");
        for (int j = 0; j < this.dataList.size(); j++) {
            HSSFRow hssfRow = this.hssfSheet.createRow(startIndex++);
            if (this.dataList.get(j) instanceof Map) {
                Map temp=(Map) dataList.get(j);
                for (int i = 0; i < columnSize; i++) {
                    Object cellValue = temp.get(fieldName[i]);
                    createHSSFCell(hssfRow, i, cellValue, true);
                }
            } else {
                Object temp=dataList.get(j);
                for (int i = 0; i < columnSize; i++) {
                    Object cellValue = getFieldValueByName(fieldName[i],temp);
                    createHSSFCell(hssfRow, i, cellValue, true);
                }
            }
        }
    }


    /**
     * 文件输出到指定路径（路径中要包含文件名）
     *
     * @param filePath
     */
    public void write2FilePath(String filePath) {
        log.info("【将生成的Excel输出到指定目录】filePath ：" + filePath);
        FileOutputStream fileOut = null;
        try {
            fileOut = new FileOutputStream(filePath);
            write2OutputStream(fileOut);
        } catch (Exception e) {
            throw new RuntimeException("将生成的Excel输出到指定目录失败");
        }
    }

    /**
     * 文件输出到流中
     *
     * @param outputStream
     */
    public void write2OutputStream(OutputStream outputStream) {

        try {
            this.hssfWorkbook.write(outputStream);
        } catch (Exception e) {
            throw new RuntimeException("hssfWorkbook 写入流中失败");
        } finally {
            IOUtils.closeQuietly(outputStream);
        }
    }

    /**
     * 文件输出到Response中
     *
     * @param response
     */
    public void write2Response(HttpServletResponse response,String fileName) {
        response.reset();
        response.setContentType("application/vnd.ms-excel");        //改成输出excel文件
//        response.setContentType("application/x-msdownload");
        try {
            response.setHeader("Content-Disposition", "attachment; filename="+ (StringUtils.isBlank(fileName)?new Date().getTime():URLEncoder.encode(fileName, "UTF-8"))+".xls");
        } catch (Exception e) {
            response.setHeader("Content-Disposition", "attachment; filename="+new Date().getTime()+".xls");
        }

        try {
            OutputStream outputStream = response.getOutputStream();
            write2OutputStream(outputStream);
        } catch (Exception e) {
            log.warn("【Manual】=>{}", e.getMessage());
        }

    }



    /**
     * 单元格赋值
     *
     * @param cell
     * @param cellValue
     */
    private static void setCellValueHandler(HSSFCell cell, Object cellValue) {
        if (cellValue == null) {
            cell.setCellValue("");
        } else if (cellValue instanceof String) {
            cell.setCellValue((String) cellValue);
        } else if (cellValue instanceof Number) {
            cell.setCellValue(cellValue instanceof Double ? (Double) cellValue : (Double.parseDouble(cellValue.toString())));
        } else if (cellValue instanceof Date) {
            cell.setCellValue((Date) cellValue);
        } else if (cellValue instanceof HSSFRichTextString) {
            cell.setCellValue((HSSFRichTextString) cellValue);
        } else if (cellValue instanceof Boolean) {
            cell.setCellValue((Boolean) cellValue);
        } else if (cellValue instanceof Calendar) {
            cell.setCellValue((Calendar) cellValue);
        } else {
            cell.setCellValue(cellValue.toString());
        }

    }

    /**
     * 根据字段名获取字段值
     *
     * @param fieldName
     * @param object
     * @return
     * @throws Exception
     */
    private static Object getFieldValueByName(String fieldName, Object object)  {

        Object value = null;
        Field field = getFieldByName(fieldName, object.getClass());
        if (field != null) {
            field.setAccessible(true);
            try {
                value = field.get(object);
            } catch (Exception e) {
               log.warn("【Manual】=>{}",e.getMessage());
            }
        } else {
            log.warn("fieldName 字段不存在");
        }
        return value;
    }


    /**
     * 根据字段名获取字段对象
     *
     * @param fieldName
     * @param clazz
     * @return
     */
    private static Field getFieldByName(String fieldName, Class<?> clazz) {
        //拿到本类的所有字段
        Field[] selfFields = clazz.getDeclaredFields();
        //如果本类中存在该字段，则返回
        for (Field field : selfFields) {
            if (field.getName().equals(fieldName)) {
                return field;
            }
        }
        //否则，查看父类中是否存在此字段，如果有则返回
        Class<?> superClazz = clazz.getSuperclass();
        if (superClazz != null && superClazz != Object.class) {
            return getFieldByName(fieldName, superClazz);
        }
        //如果本类和父类都没有，则返回空
        return null;
    }


    /**
     * 测试实例
     *
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        List<Map> mapList = new ArrayList<Map>();
        for (int i = 1; i <= 50; i++) {
            Map map = new HashMap();
            map.put("string", "name" + i);
            map.put("boolean", i % 2 == 1 ? true : false);
            map.put("date", new Date());
            map.put("dateTime", new Date());
            map.put("money", ((double) (100 + i)) / 3);
            map.put("double", ((double) (100 + i)) / 3);
            map.put("baiFen", ((double) i) / 99);
            map.put("keXueJiStyle", 2843943354673424324L + i);
            mapList.add(map);
        }

        PoiExcelCreateUtil poi = new PoiExcelCreateUtil(8);
        CellStyle titleStyle = poi.createCellStyle((short) 500, poi.FONT_WEIGHT_BOLE, "楷体");
        CellStyle headStyle = poi.createCellStyle(poi.FONT_HEIGHT_DEFAULT, poi.FONT_WEIGHT_BOLE, poi.FONT_NAME_SONG);
        CellStyle bodyStyle = poi.createCellStyle(poi.FONT_HEIGHT_DEFAULT, poi.FONT_WEIGHT_NORMAL, poi.FONT_NAME_SONG);
        CellStyle dataStyle = poi.createCellStyle(poi.FONT_HEIGHT_DEFAULT, poi.FONT_WEIGHT_NORMAL, poi.FONT_NAME_SONG, poi.FORMAT_DATE_DEFAULT);
        CellStyle dataTimeStyle = poi.createCellStyle(poi.FONT_HEIGHT_DEFAULT, poi.FONT_WEIGHT_NORMAL, poi.FONT_NAME_SONG, poi.FORMAT_DATE_TIME_DEFAULT);
        CellStyle moneyStyle = poi.createCellStyle(poi.FONT_HEIGHT_DEFAULT, poi.FONT_WEIGHT_NORMAL, poi.FONT_NAME_SONG, poi.FORMAT_MONEY_DEFAULT);
        CellStyle doubleStyle = poi.createCellStyle(poi.FONT_HEIGHT_DEFAULT, poi.FONT_WEIGHT_NORMAL, poi.FONT_NAME_SONG, "0.00");
        CellStyle baiFenStyle = poi.createCellStyle(poi.FONT_HEIGHT_DEFAULT, poi.FONT_WEIGHT_NORMAL, poi.FONT_NAME_SONG, "0.00%");
        CellStyle keXueJiStyle = poi.createCellStyle(poi.FONT_HEIGHT_DEFAULT, poi.FONT_WEIGHT_NORMAL, poi.FONT_NAME_SONG, "0.00E+00");
        poi.createSheet("sheet-1",
                "表格1",
                new String[]{"string", "boolean", "date", "dateTime", "money", "double", "baiFen", "keXueJiStyle"},
                new String[]{"string", "boolean", "date", "dateTime", "money", "double", "baiFen", "keXueJiStyle"},
                new int[]{4000, 4000, 4000, 8000, 4000, 4000, 4000, 6000},
                titleStyle, headStyle,
                new CellStyle[]{bodyStyle, bodyStyle, dataStyle, dataTimeStyle, moneyStyle, doubleStyle, baiFenStyle, keXueJiStyle},
                mapList
        );
        poi.setColumnSize(5);
        poi.createSheet("sheet-2",
                null,
                new String[]{"string", "boolean", "date", "dateTime", "money", "double", "baiFen", "keXueJiStyle"},
                new String[]{"string", "boolean", "date", "dateTime", "money", "double", "baiFen", "keXueJiStyle"},
                new int[]{4000, 4000, 4000, 8000, 4000, 4000, 4000, 6000},
                titleStyle, headStyle,
                new CellStyle[]{bodyStyle, bodyStyle, dataStyle, dataTimeStyle, moneyStyle, doubleStyle, baiFenStyle, keXueJiStyle},
                mapList
        );
        poi.write2FilePath("c:\\first.xls");

    }


}


